﻿Imports MySql.Data
Imports System.Text.RegularExpressions

Namespace PPE3


    Public Class ClassMySqlConnect


#Region "Champs"

        Public D As Integer = 1
        Private Cnx As New MySql.Data.MySqlClient.MySqlConnection
        Public Cmd As New MySql.Data.MySqlClient.MySqlCommand
        Private DataAdapt As New MySql.Data.MySqlClient.MySqlDataAdapter
        Private MonDataSet As New Data.DataSet
        Private MaChaine As String
        Private cptvue As Integer = 0

#End Region

#Region "Constructeurs et connexion"

        Public Sub New()
            MaChaine = "Data Source=hostingmysql114.amen.fr;database=853339_depannage;User Id=853339_depanneur;Password=Linux_710"
        End Sub

        Public Sub New(ByVal login As String, ByVal mdp As String)
            'MaChaine = "Data Source=localhost;Initial Catalog=GestStageEtud;Persist Security Info=True;User ID=" & login & ";Password=" & mdp & ""
            MaChaine = "Data Source=hostingmysql114.amen.fr;database=853339_depannage;UID=853339_depanneur;Password=071064 "
            'MaChaine = "Data Source=localhost;Initial Catalog=853339_depannage;User ID=root;"
        End Sub

        Public Sub Se_Connecter_MySqlServer()
                Cnx.ConnectionString = MaChaine
                Cnx.Open()
                'MonDataSet.Clear()
                Cmd.CommandType = CommandType.Text
                Cmd.Connection = Cnx


        End Sub

        Public Sub Se_Deconnecter_MySqlServer()
            Cnx.Close()
        End Sub

#End Region

#Region "Requêtes Select et Action"

        Public Sub procedureStockee(ByVal nomProc As String, ByVal monData As DataGridView)

            Try

                Cmd = Cnx.CreateCommand()
                Cmd.CommandText = nomProc
                Cmd.CommandType = CommandType.StoredProcedure
                monData.Rows.Clear()
                Dim MyReader As MySql.Data.MySqlClient.MySqlDataReader = Cmd.ExecuteReader()
                Do While MyReader.Read()
                    monData.Rows.Add(MyReader(0), MyReader(1), MyReader(2))


                Loop

                MyReader.Close()

            Catch ex As Exception
                MessageBox.Show(ex.Message())
            End Try

        End Sub

        Public Function RemplaceGuillemetParVide(ByRef unechaine As String) As String
            Dim ch As String
            ch = unechaine.Replace(Chr(160), " ")
            Return ch
        End Function

        Public Function RemplaceCoteParVide(ByRef unechaine As String) As String
            Dim ch As String
            ch = unechaine.Replace("'", " ")
            Return ch
        End Function

        Public Function Generer_Cle_Primaire(ByVal nomcle As String, ByVal nomtable As String) As Integer
            Try

                Se_Connecter_MySqlServer()
                Cmd.CommandText = "Select Max(" & nomcle & ") from " & nomtable
                Dim id As Integer = (CInt(Cmd.ExecuteScalar) + 1)
                Se_Deconnecter_MySqlServer()
                Return id

            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try

        End Function

        Public Sub Chargement_ComboBox(combox As ComboBox, requete As String, vue As String, valueMember As String, DisplayMember As String)

            Cmd = Cnx.CreateCommand()
            Cmd.CommandText = requete
            DataAdapt.SelectCommand = Cmd
            DataAdapt.Fill(MonDataSet, vue)
            combox.DataSource = MonDataSet.Tables(vue)

            combox.ValueMember = valueMember
            combox.DisplayMember = DisplayMember
        End Sub

        Public Sub Executer_Requete_Action(ByVal marequeteaction As String)
            Cmd.CommandText = marequeteaction
            Try
                Cmd.ExecuteNonQuery()
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
        End Sub

        Public Function DateAtelierCurrentRow(id As String) As String
            Dim requete As String = "SELECT DateHeureDebIntervention FROM date WHERE IdIntervention = " & id & " "

            Try
                Cmd.CommandText = requete
                Dim resutl As String = Cmd.ExecuteScalar().ToString
                Return resutl

            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
        End Function

        Public Function Executer_Requete_TIMEDIFF_Finis(id As String, DateFin As String, DateDebut As String) As String
            Dim requete As String = "SELECT TIMEDIFF( DateHeureFinIntervention , DateHeureDebIntervention ) FROM date WHERE IdIntervention = " & id & " "

            Try
                Cmd.CommandText = requete
                Dim resutl As String = Cmd.ExecuteScalar().ToString
                Return resutl
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try


        End Function

        Public Function Executer_Requete_TIMEDIFF_EnCour(id As String, dateTime As String) As String
            Dim requete As String = "SELECT TIMEDIFF( NOW() ,  DateHeureDebIntervention ) FROM date WHERE IdIntervention = " & id & " "


            Try
                Cmd.CommandText = requete
                Dim resutl As String = Cmd.ExecuteScalar().ToString()
                Return resutl

            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try


        End Function

        Public Function ExecuteScalar(requete As String) As Object
            Dim resutl As String
            Try
                Cmd.CommandText = requete
                resutl = Cmd.ExecuteScalar()
                Return resutl
            Catch ex As Exception
                If resutl = Nothing Then

                    MessageBox.Show(ex.Message)
                End If
                Return resutl
            End Try

        End Function

        Public Function Executer_Requete_Select(ByVal marequeteselect As String, vue As String) As Data.DataTable
            Dim result As Data.DataTable
            Try
                Cmd.CommandText = marequeteselect
                DataAdapt.SelectCommand = Cmd
                DataAdapt.Fill(MonDataSet, vue)

                cptvue = cptvue + 1
                result = MonDataSet.Tables(vue)
                Return result
            Catch ex As Exception
                MessageBox.Show("l'erreur est " & ex.Message)
            End Try


        End Function

        Public Sub viderVue(vue As String)
            Try
                MonDataSet.Tables(vue).Clear()
            Catch ex As Exception

            End Try

        End Sub




#End Region

#Region "Gestion de l'interface"
        Public Sub Initialiser_TextBoxs(ByVal conteneur As Control)

            For Each ctrl As Control In conteneur.Controls
                If TypeOf ctrl Is TextBox Then
                    ctrl.Text = ""
                End If
            Next
        End Sub



        Public Sub AlimenterComboBox(ByRef macombo As ComboBox, ByVal marequete As String, ByVal mondisplay As String, ByVal mavaleur As String, vue As String)

            macombo.DataSource = Executer_Requete_Select(marequete, vue)
            macombo.DisplayMember = mondisplay
            If mavaleur <> "" Then
                macombo.ValueMember = mavaleur
            End If

        End Sub
#End Region

#Region "Gestion du DataSet"

        Public Function GetChamp_DataTable(ByRef lesdonnees As DataTable, ByVal ligne As Integer, ByVal colonne As Integer) As String
            'Try
            Return CStr(lesdonnees.Rows(ligne).Item(colonne))
            'Catch ex As Exception
            'MsgBox(ex)
            ' Return 0
            ' End Try

        End Function

        Public Sub Vider_Dataset()
            MonDataSet.Clear()
            cptvue = 0
        End Sub
#End Region

        Public Function NOCHIFFRE(ByVal adresse As String) As Boolean
            Dim myRegex As System.Text.RegularExpressions.Regex
            myRegex = New Regex("^([\w]+)$")
            '([\w]+) ==> caractère alphanumérique apparaissant une fois ou plus 
            Return myRegex.IsMatch(adresse) 'retourne true ou false selon la vérification 
        End Function


        Public Function YESCHIFFRE(ByVal adresse As String) As Boolean
            Dim myRegex As System.Text.RegularExpressions.Regex
            myRegex = New Regex("^(\d+(?:[.,]\d+)?)$")
            '([\w]+) ==> caractère alphanumérique apparaissant une fois ou plus 
            Return myRegex.IsMatch(adresse) 'retourne true ou false selon la vérification 
        End Function

    End Class
End Namespace

